import os
import traceback
import math
import pandas as pd
import numpy as np
import pdfplumber
import pymysql as pm
import xlwt
from sqlalchemy import create_engine
from PyPDF2 import PdfFileWriter,PdfFileReader
from configs.cfg import *
from tools.insert_update import insert_sql


class Pd2DB:
    engine = create_engine(f'mysql+pymysql://{mysql_name}:{mysql_pwd}@{mysql_host}:{mysql_port}/spiderdb?charset=utf8')

    def __init__(self):
        self.conn = pm.connect(host=mysql_host, user=mysql_name, password=mysql_pwd,
                               database='spiderdb', port=mysql_port, charset='utf8')
        self.cur = self.conn.cursor()

    def create_table(self):
        fields = (
            'CIN', 'COMPANY_NAME', 'DATE_OF_REGISTRATION', 'MONTH_NAME', 'STATE', 'ROC', 'COMPANY_STATUS', 'CATEGORY',
            'CLASS', 'COMPANY_TYPE', 'AUTHORIZED_CAPITAL', 'PAIDUP_CAPITAL', 'ACTIVITY_CODE', 'ACTIVITY_DESCRIPTION',
            'REGISTERED_OFFICE_ADDRESS', 'EMAIL')
        sql = f'''CREATE TABLE IF NOT EXISTS india_registered_company(
        id int unsigned auto_increment,
        {fields[0]} varchar(255) NOT NULL COMMENT '',
        {fields[1]} varchar(255) NOT NULL COMMENT '公司名称',
        {fields[2]} date NOT NULL COMMENT '注册日期',
        {fields[3]} varchar(255) COMMENT '月份',
        {fields[4]} varchar(255) COMMENT '州',
        {fields[5]} varchar(255) COMMENT '中华民国',
        {fields[6]} varchar(255) COMMENT '公司状态',
        {fields[7]} varchar(255) COMMENT '分类',
        {fields[8]} varchar(255) COMMENT '类别',
        {fields[9]} varchar(255) COMMENT '公司类型',
        {fields[10]} varchar(255) COMMENT '法定资本',
        {fields[11]} varchar(255) COMMENT '实收资本',
        {fields[12]} varchar(255) COMMENT '机构代码',
        {fields[13]} varchar(255) COMMENT '机构描述',
        {fields[14]} varchar(255) COMMENT '注册办公室地址',
        {fields[15]} varchar(255) COMMENT '电子邮件',
        PRIMARY KEY (id)
        )ENGINE=InnoDB DEFAULT CHARSET='utf8';
        '''
        try:
            self.cur.execute(sql)
            self.conn.commit()
            print('Update Succeeded')
        except:
            print('Update Failed')
        finally:
            self.cur.close()
            self.conn.close()

    def insert_datas(self, datas):
        table_name = 'india_registered_company'
        # 定义要插入的字段
        fields = (
            'CIN', 'COMPANY_NAME', 'DATE_OF_REGISTRATION', 'MONTH_NAME', 'STATE', 'ROC', 'COMPANY_STATUS', 'CATEGORY',
            'CLASS', 'COMPANY_TYPE', 'AUTHORIZED_CAPITAL', 'PAIDUP_CAPITAL', 'ACTIVITY_CODE', 'ACTIVITY_DESCRIPTION',
            'REGISTERED_OFFICE_ADDRESS', 'EMAIL')
        try:
            self.conn.ping()
            sql = insert_sql(table_name, fields, datas)
            self.cur.execute(sql)
            self.conn.commit()
            print('Insert Succeeded')
        except:
            traceback.print_exc()
            self.conn.rollback()

    def getfiles(self, dirPath):
        files = os.listdir(dirPath)
        filepaths = [dirPath + filepath for filepath in files]
        return filepaths

    def handle_file(self, filepath):
        '''将表格中的数据修剪成标准格式的数据，便于后续处理'''
        # 默认读取Excel的第一个表单
        print(filepath)
        datas = pd.read_excel(filepath, sheet_name=None, header=None)
        for tmp_datas in datas.values():
            # 判断表格是否为空
            if not tmp_datas.empty:
                # 删除表格中大多数为NaN的列和行
                tmp = tmp_datas.dropna(axis=0, thresh=8).dropna(how='all', axis=1)
                # 设置索引从1开始
                if not tmp.empty:
                    tmp.index = range(1, len(tmp) + 1)
                    # 判断第一个值是否为S.No/SL.No./S.No.（定位取值）
                    if tmp.iloc[0, 0].lower() == 's.no' or tmp.iloc[0, 0].lower() == 'sl.no.' or tmp.iloc[
                        0, 0].lower() == 's.no.' or tmp.iloc[0, 0].lower() == 'sl. no.':
                        # 删除num列
                        # tmp = tmp.iloc[:, 1:].values.tolist()
                        tmp = tmp.iloc[:, 1:].values.tolist()
                    else:
                        tmp = tmp.values.tolist()
                    # print(tmp)
                    df = pd.DataFrame(tmp)
                    df.columns = np.array([i.lower().strip().replace(' ', '_') for i in df.iloc[0].values.tolist()])
                    df = df.drop(0)
                    if 'cin' in df.columns.values or 'fcin' in df.columns.values or 'fcrn' in df.columns.values:
                        print('to cin')
                        df = df.rename(
                            columns={'fcin': 'cin', 'fcrn': 'cin', 'date_of_incorporation': 'date_of_registration',
                                     'date': 'date_of_registration', 'roc_location': 'roc',
                                     'category': 'company_category', 'company_sub-category': 'sub_category',
                                     'class': 'company_class', 'paidup_capital': 'paid_capital',
                                     'number_of_members_(applicable_only_in_case_of_co._without_share_capital)': 'number_of_members',
                                     'industrial_description': 'activity_description',
                                     'description': 'activity_description',
                                     'registered_office_address': 'company_address', 'address': 'company_address',
                                     'foreign_company_present_address_in_india': 'company_address',
                                     'type': 'company_type', 'status': 'company_status',
                                     'industrial_activity_code': 'activity_code', 'activity': 'activity_code',
                                     'type_of_office': 'office_type', 'company_email_id': 'email'})

                        df.to_sql(name='india_company_cin', con=self.engine, if_exists='append', index=False,
                                  chunksize=20000)
                    elif 'llpin' in df.columns.values:
                        print('to pin')
                        df = df.rename(columns={'roc_location': 'roc', 'status': 'llp_status',
                                                'description': 'activity_description',
                                                'industrial_activity_description': 'activity_description',
                                                'industrial_activity_code': 'activity_code',
                                                'registered_office_address': 'address'})
                        df.to_sql(name='india_company_pin', con=self.engine, if_exists='append', index=False,
                                  chunksize=20000)
                    else:
                        print(df.columns.values)
                        print('not in cin/pin')

    def pdf2excel(self, filepath):
        print(filepath)
        workbook = xlwt.Workbook()
        # 添加sheet
        # sheet = workbook.add_sheet('Sheet1')
        pdf = pdfplumber.open(filepath)
        for page in pdf.pages:
            for table in page.extract_tables():
                df = pd.DataFrame(table)
                df.columns = np.array([i.lower().strip().replace(' ', '_') for i in df.iloc[0].values.tolist()])
                df = df.iloc[:, 1:]
                df = df.drop(index=0)
                df = df.rename(columns={'date_of_incorporation':'date_of_registration','cin/fcrn':'cin_fcrn_llpin_fllpin','cin/fcrn/llpin/fllpin':'cin_fcrn_llpin_fllpin'})
                df.to_sql(name='india_company_history', con=self.engine, if_exists='append', index=False,
                          chunksize=20000)
            print(page,' Succeeded')

    def pdf2excelWithPyPdf(self):
        filepath='D:/Download/List of Companies registered for Year 2014.pdf'
        # pdf_writer = PdfFileWriter()
        pdf_reader = PdfFileReader(filepath)
        pages = pdf_reader.getNumPages()
        for page in range(pages):
            data = pdf_reader.getPage(page).extractText()
            contents = data.replace('\n',',').replace('Sr.No,CIN/FCRN,Name of the entity,Date of Registration,','').split('/2014')
            li = []
            for content in contents:
                base_info = content.split(',')
                # print(base_info)
                tmp_li = list(filter(None, base_info))
                if tmp_li:
                    # sn = tmp_li[0]
                    cin = ''.join(tmp_li[1:-2])
                    company_name = tmp_li[-2]
                    date_registration = tmp_li[-1] + '/2014'
                    li.append((cin,company_name,date_registration))

            table_name = 'india_company_history'
            fields = ('cin_fcrn_llpin_fllpin','name_of_the_entity','date_of_registration')
            try:
                sql = insert_sql(table_name,fields,li)
                self.cur.execute(sql)
                self.conn.commit()
                print('insert succeeded')
            except:
                traceback.print_exc()
                # pass

    def run(self):
        # e_filepaths = self.getfiles('D:/Download/India/')
        # list(map(self.handle_file, filepaths))
        # 调试单个excel文件
        # self.handle_file('D:/Download/India/Master Details of Companies-LLPs Registered -June, 2017.xlsx')
        # p_filepaths = self.getfiles('D:/Download/IndiaHistory/')
        # list(map(self.pdf2excel, p_filepaths))
        # self.pdf2excel('D:/Download/IndiaHistory/List of Companies registered for Year 2013.pdf')
        self.pdf2excelWithPyPdf()

if __name__ == '__main__':
    db = Pd2DB()
    db.run()


if __name__ == '__main__':
    db = Pd2DB()
    db.run()
